﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Mapeo;
using System.Data;
using NHibernate;
using MySql.Data.MySqlClient;
using BE;
using NHibernate.Criterion;

namespace DAL
{
    public class CertificadoProgramas_DAL: Controlador
    {


        public DataTable listadoAlumnos(String Codigo, String Nombre, String Programa, String Ciclo, String Aula, bool NombreCambiado)
        {
            using (ISession Sesion = m_SessionFactory.OpenSession())
            {
                using (ITransaction Trans = Sesion.BeginTransaction())
                {
                    MySqlCommand SqlCmd = new MySqlCommand();
                    SqlCmd.Connection = (MySqlConnection)Sesion.Connection;
                    Trans.Enlist(SqlCmd);
                    SqlCmd.CommandText = "sp_ObtenerAlumnoProgramas";
                    SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlCmd.Parameters.AddWithValue("@in_CodAlumno", Codigo);
                    SqlCmd.Parameters.AddWithValue("@in_Nombres", Nombre);
                    SqlCmd.Parameters.AddWithValue("@in_IDPrograma", Programa);
                    SqlCmd.Parameters.AddWithValue("@in_Ciclo", Ciclo);
                    SqlCmd.Parameters.AddWithValue("@in_Aula", Aula);
                    SqlCmd.Parameters.AddWithValue("@in_NombreCambiado", NombreCambiado);
                    SqlCmd.ExecuteNonQuery();
                    MySqlDataAdapter da = new MySqlDataAdapter(SqlCmd);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    Trans.Commit();
                    return dt;
                }
            }
        }
        public DataTable obtenerCertificadoAlumno(String Codigo)
        {
            using (ISession Sesion = m_SessionFactory.OpenSession())
            {
                using (ITransaction Trans = Sesion.BeginTransaction())
                {
                    MySqlCommand SqlCmd = new MySqlCommand();
                    SqlCmd.Connection = (MySqlConnection)Sesion.Connection;
                    Trans.Enlist(SqlCmd);
                    SqlCmd.CommandText = "sp_ObtenerCertficadoProgramas";
                    SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlCmd.Parameters.AddWithValue("@in_CodAlumno", Codigo);
                    SqlCmd.ExecuteNonQuery();
                    MySqlDataAdapter da = new MySqlDataAdapter(SqlCmd);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    Trans.Commit();
                    return dt;
                }
            }
        }

        public DataTable ObtenerAulaPrograma(String IDPrograma, String Ciclo)
        {
            using (ISession Sesion = m_SessionFactory.OpenSession())
            {
                using (ITransaction Trans = Sesion.BeginTransaction())
                {
                    MySqlCommand SqlCmd = new MySqlCommand();
                    SqlCmd.Connection = (MySqlConnection)Sesion.Connection;
                    Trans.Enlist(SqlCmd);
                    SqlCmd.CommandText = "sp_ObtenerAula_Programa";
                    SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlCmd.Parameters.AddWithValue("@in_IdPrograma", IDPrograma);
                    SqlCmd.Parameters.AddWithValue("@in_IdCiclo", Ciclo);
                    SqlCmd.ExecuteNonQuery();
                    MySqlDataAdapter da = new MySqlDataAdapter(SqlCmd);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    Trans.Commit();
                    return dt;
                }
            }
        }
        public List<Programas> ObtenerProgramas()
        {
            try
            {
                using (ISession Sesion = m_SessionFactory.OpenSession())
                {
                    ICriteria criteria = Sesion.CreateCriteria("Programas")
                        .AddOrder(Order.Asc("ID"));
                    //criteria.SetMaxResults(100);
                    List<Programas> listado = (List<Programas>)criteria.List<Programas>();
                    return listado;
                }
            }
            catch (Exception)
            {

                throw;
            }
        }
    
    }
}
